import numpy
import pymysql
import tushare
import datetime


def basic_update():
    basic_db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_basic', charset='utf8')
    basic_cursor = basic_db.cursor()

    insert_sql = "REPLACE INTO STOCK_BASIC (code,name,industry,area,pe,outstanding,totals,totalassets,liquidassets,fixedassets,reserved,reservedpershare,esp,bvps,pb,timetomarket,undp,perundp,rev,profit,gpr,npr,holders) VALUES "

    basic_data = tushare.get_stock_basics()
    for i in range(len(basic_data)):
        data = basic_data.iloc[i]
        code = data.name
        name = data['name']
        industry = data['industry']
        area = data['area']
        pe = data['pe']
        outstanding = data['outstanding']
        totals = data['totals']
        totalassets = data['totalAssets']
        liquidassets = data['liquidAssets']
        fixedassets = data['fixedAssets']
        reserved = data['reserved']
        reservedpershare = data['reservedPerShare']
        esp = data['esp'] if type(data['esp']) == numpy.float64 else 0
        bvps = data['bvps']
        pb = data['pb']
        timetomarket = data['timeToMarket']
        undp = data['undp']
        perundp = data['perundp']
        rev = data['rev']
        profit = data['profit']
        gpr = data['gpr']
        npr = data['npr']
        holders = data['holders']

        insert_data = (
            code, name, industry, area, pe, outstanding, totals, totalassets, liquidassets, fixedassets, reserved,
            reservedpershare, esp, bvps, pb, timetomarket, undp, perundp, rev, profit, gpr, npr, holders)
        insert_sql += str(insert_data) + ','

    insert_sql = insert_sql.rstrip(',')

    basic_cursor.execute(insert_sql)
    basic_db.commit()
    basic_db.close()



def index_update():
    #指数数据库
    index_db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_index', charset='utf8')
    index_cursor = index_db.cursor()

    index_basics = tushare.get_index()
    index_codes = []

    for i in range(len(index_basics)):
        index_info = index_basics.iloc[i]
        index_codes.append(index_info['code'])

    now_date = datetime.datetime.now().strftime('%Y-%m-%d')

    query_sql = "show tables"
    index_cursor.execute(query_sql)
    query_result = index_cursor.fetchall()
    query_codes = []
    for query_code in query_result:
        query_codes.append(query_code[0])

    for code in index_codes:
        if code not in query_codes:
            create_sql = "CREATE TABLE IF NOT EXISTS {0} LIKE `000001`".format(code)
            index_cursor.execute(create_sql)
            index_db.commit()
            index_data = tushare.get_k_data(code,start='1990-12-19',end=now_date,index=True)
            insert_sql = "INSERT IGNORE INTO `{0}` (DATE,OPEN,CLOSE,HIGH,LOW,VOLUME,CODE) VALUES ".format(code)

            for i in range(len(index_data)):
                data = index_data.iloc[i]
                date = data['date']
                open = data['open']
                close = data['close']
                high = data['high']
                low = data['low']
                volume = data['volume']
                code = data['code']

                insert_data = (date, open, close, high, low, volume, code)

                insert_sql += str(insert_data) + ','

            insert_sql = insert_sql.rstrip(',')

            index_cursor.execute(insert_sql)
            index_db.commit()
        else:
            query_date_sql = "SELECT MAX(DATE) FROM `{0}`".format(code)
            index_cursor.execute(query_date_sql)
            query_date_result = index_cursor.fetchone()
            query_date = query_date_result[0]
            start_date = datetime.datetime.strftime(query_date, '%Y-%m-%d')
            index_data = tushare.get_k_data(code,start=start_date,end=now_date,index=True)

            insert_sql = "INSERT IGNORE INTO `{0}` (DATE,OPEN,CLOSE,HIGH,LOW,VOLUME,CODE) VALUES ".format(code)

            for i in range(len(index_data)):
                data = index_data.iloc[i]
                date = data['date']
                open = data['open']
                close = data['close']
                high = data['high']
                low = data['low']
                volume = data['volume']
                code = data['code']

                insert_data = (date, open, close, high, low, volume, code)

                insert_sql += str(insert_data) + ','

            insert_sql = insert_sql.rstrip(',')

            index_cursor.execute(insert_sql)
            index_db.commit()
        print("Index {0} is updated".format(code))
    index_db.close()




def hist_update():
    #基础数据库
    basic_db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_basic', charset='utf8')
    basic_cursor = basic_db.cursor()

    query_sql = "select code from stock_basic"
    basic_cursor.execute(query_sql)

    code_result = basic_cursor.fetchall()

    codes = []

    for code in code_result:
        codes.append(code[0])

    #股票行情数据库
    hist_db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_hist', charset='utf8')
    hist_cursor = hist_db.cursor()

    query_hist_sql = "show tables"
    hist_cursor.execute(query_hist_sql)

    query_hist_result = hist_cursor.fetchall()

    hist_codes = []

    for code in query_hist_result:
        hist_codes.append(code[0])

    now_date = datetime.datetime.now().strftime('%Y-%m-%d')

    for code in codes:
        if code not in hist_codes:
            create_table_sql = """
                                CREATE TABLE IF NOT EXISTS `{0}` (
                                DATE DATE NOT NULL,
                                OPEN FLOAT,
                                CLOSE FLOAT,
                                HIGH FLOAT,
                                LOW FLOAT,
                                VOLUME FLOAT,
                                CODE CHAR(6)
                                )
                                """.format(code)
            hist_cursor.execute(create_table_sql)
            hist_db.commit()
        else:
            query_table_sql = "select count(*) from `{0}`".format(code)
            hist_cursor.execute(query_table_sql)
            query_table_result = hist_cursor.fetchone()
            if query_table_result[0] == 0:
                timetomarket_sql = "SELECT timetomarket FROM stock_basic where code = {0}".format(code)
                basic_cursor.execute(timetomarket_sql)
                timetomarket_result = basic_cursor.fetchone()
                timetomarket = timetomarket_result[0]

                if timetomarket == None:
                    print(code)
                    pass
                else:

                    start_date = datetime.datetime.strftime(timetomarket,'%Y-%m-%d')


                    hist_data = tushare.get_k_data(code,start=start_date,end=now_date)

                    insert_sql = "INSERT IGNORE INTO `{0}` (DATE,OPEN,CLOSE,HIGH,LOW,VOLUME,CODE) VALUES ".format(code)

                    for i in range(len(hist_data)):
                        data = hist_data.iloc[i]
                        date = data['date']
                        open = data['open']
                        close = data['close']
                        high = data['high']
                        low = data['low']
                        volume = data['volume']
                        code = data['code']

                        insert_data = (date, open, close, high, low, volume, code)

                        insert_sql += str(insert_data) + ','

                    insert_sql = insert_sql.rstrip(',')

                    hist_cursor.execute(insert_sql)
                    hist_db.commit()

            else:
                query_max_sql = "select max(date) from `{0}`".format(code)
                hist_cursor.execute(query_max_sql)
                query_max_result = hist_cursor.fetchone()
                query_date = query_max_result[0]
                start_date = datetime.datetime.strftime(query_date, '%Y-%m-%d')
                hist_data = tushare.get_k_data(code,start=start_date,end=now_date)
                insert_sql = "INSERT IGNORE INTO `{0}` (DATE,OPEN,CLOSE,HIGH,LOW,VOLUME,CODE) VALUES ".format(code)

                for i in range(len(hist_data)):
                    data = hist_data.iloc[i]
                    date = data['date']
                    open = data['open']
                    close = data['close']
                    high = data['high']
                    low = data['low']
                    volume = data['volume']
                    code = data['code']

                    insert_data = (date, open, close, high, low, volume, code)

                    insert_sql += str(insert_data) + ','

                insert_sql = insert_sql.rstrip(',')
                hist_cursor.execute(insert_sql)
                hist_db.commit()
        print("Stock {0} is updated".format(code))

    hist_db.close()
    basic_db.close()


if __name__ == '__main__':
    index_update()
    print("Index is updated!!!")
    basic_update()
    print("Stock Basic is updated!!!")
    hist_update()
    print("Stock Hist is updated!!!")